#!/usr/bin/python
# -*- coding: UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""

Authors: zhangjianjun
Date:    01/18/2021 11:01 AM
"""

import xlrd
import time
import os
import hashlib
import pymysql
from dbConnecttion.MysqlConn import Mysql
from downloadtask import downloadfiletask


def proc_excel_data(file_name):
    """
    parse excel, import data
    parse: file_name
    return: list
    """
    to_email_name_list = []
    cc_email_name_list = []
    email_dict = {}
    try:
        data = xlrd.open_workbook(file_name)
        table_one = data.sheet_by_name("Sheet1")
        row_number = table_one.nrows
        for i in range(1, row_number):
            if table_one.cell(i, 1).value == 1:
                to_email_name_list.append(table_one.cell(i, 0).value)
            else:
                cc_email_name_list.append(table_one.cell(i, 0).value)
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    email_dict["to"] = to_email_name_list
    email_dict["cc"] = cc_email_name_list
    return email_dict


def cur_date():
    """
    current date
    :return createTime: string
    """
    create_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    return create_time


def get_issue_excel():
    """
        import excel
        :param
        :return none
    """
    file_dir = "issue_statistics"
    downloadfiletask.download_excel(file_dir)
    mysql = Mysql()
    files = os.listdir('./' + file_dir)
    for filename in files:
        file_vaule = "./%s/%s" % (file_dir, filename)
        with open(file_vaule, 'rb') as f:
            sha1obj = hashlib.sha1()
            sha1obj.update(f.read())
            hash_value = sha1obj.hexdigest()
            print(filename, hash_value)
            f.close()
        sql = "select file_hash from cve_file_hash where file_name = %s"
        val = (filename,)
        file_hash = mysql.getOne(sql, val)
        if file_hash:
            if hash_value == file_hash['file_hash']:
                print("File parsed:" + filename)
                os.remove(file_vaule)
                continue
        sql = "insert into cve_file_hash (file_name, file_hash) values (%s, %s)"
        val = (filename, hash_value)
        mysql.insertOne(sql, val)
        mysql.dispose()
        email_dict = proc_excel_data(file_vaule)
        if email_dict is not None and len(email_dict) > 0:
            to_email_name_list = email_dict["to"]
            cc_email_name_list = email_dict["cc"]
        else:
            continue
        if to_email_name_list is not None and len(to_email_name_list) > 0:
            del_sql = "delete from cve_issue_statistics_mail_list where email_type = %s"
            mysql.delete(del_sql, (1,))
            mysql.dispose()
            for email_name in to_email_name_list:
                try:
                    insert_sql = "insert into cve_issue_statistics_mail_list" \
                                 "(email_name, email_type, create_time) values(%s,%s, %s)"
                    val = (email_name, 1, cur_date())
                    mysql.insertOne(insert_sql, val)
                    mysql.dispose()
                except pymysql.err.IntegrityError as e:
                    print(e)
                    mysql.dispose(2)
        del_sql = "delete from cve_issue_statistics_mail_list where email_type = %s"
        mysql.delete(del_sql, (2,))
        mysql.dispose()
        if cc_email_name_list is not None and len(cc_email_name_list) > 0:
            for email_name in cc_email_name_list:
                try:
                    insert_sql = "insert into cve_issue_statistics_mail_list" \
                                 "(email_name, email_type, create_time) values(%s,%s, %s)"
                    val = (email_name, 2, cur_date())
                    mysql.insertOne(insert_sql, val)
                    mysql.dispose()
                except pymysql.err.IntegrityError as e:
                    print(e)
                    mysql.dispose(2)
        os.remove(file_vaule)
    mysql.close()
